<?php

class It_Model_Equipment extends Zend_Db_Table_Abstract
{
    protected $_name = 'equipment';
    // chua su dung
    public function getAllData($project, $field, $page, $row){
        $query = $this->select()
                        ->setIntegrityCheck(false)
                        ->from(array('e' => $this->_name),$field)
                        ->joinLeft(array('c'=>'category'), 'c.categoryId = e.categoryId' , array('categoryName'))
                        ->joinInner(array('eq'=>'equipment_project'), 'eq.equipmentId = e.equipmentId and eq.projectId IN ('.implode(',' ,$project).')', array())
                        ->group('e.equipmentId')
                        ->limitPage($page, $row);
        return $this->fetchAll($query);
    }
    public function countAllData($project){
        $query = $this->select()
                        ->setIntegrityCheck(false)
                        ->from(array('e' => $this->_name), 'count(e.equipmentId) as Total')
                        ->joinLeft(array('c'=>'category'), 'c.categoryId = e.categoryId', array())
                        ->joinInner(array('eq'=>'equipment_project'), 'eq.equipmentId = e.equipmentId and eq.projectId IN ('.implode(',' ,$project).')', array());
        
        return $this->fetchRow($query);
    }
    public function getSearchData($keyword, $categoryId, $projectId, $owner, $project, $field, $page, $row){
        $query = $this->select()
                        ->setIntegrityCheck(false)
                        ->from(array('e' => $this->_name),$field)
                        ->joinLeft(array('c'=>'category'), 'c.categoryId = e.categoryId' , array('categoryName'))
                        ->joinInner(array('eq'=>'equipment_project'), 'eq.equipmentId = e.equipmentId and eq.projectId IN ('.implode(',' ,$project).')', array())
                        ->joinInner(array('p'=>'project'), 'eq.projectId = p.projectId', array())
                        ->joinInner(array('i'=>'items'), 'e.equipmentId = i.equipmentId', array())
                        ->group('e.equipmentId')
                        ->limitPage($page, $row);
        if($keyword){
            $query->where("e.equipmentName LIKE ? OR e.code LIKE ? OR e.function LIKE ? OR e.position LIKE ? 
    					OR e.note LIKE ? OR e.ip LIKE ? OR e.iplan1 LIKE ? OR e.iplan2 LIKE ? OR e.endUser LIKE ? 
                        OR i.itemName LIKE ? OR i.history LIKE ? OR i.info LIKE ? OR i.owner LIKE ? 
                        OR p.projectName LIKE ?","%$keyword%");		
        }
        if($categoryId){
            if(is_array($categoryId)){
                $query->where('e.categoryId IN ('.implode(",",$categoryId).')');
            }else{
                $query->where('e.categoryId = ?',$categoryId);
            }
        }
        if($projectId){
            if(is_array($projectId)){
                $query->where('eq.projectId IN ('.implode(",",$projectId).')');
            }else{
                $query->where('eq.projectId = ?',$projectId);
            }
        }
        if($owner){
            foreach($owner as $key=>$row){
                $owner[$key] = '"'.$row.'"';
            }
            $query->where('i.owner IN ('.implode(",",$owner).')');
        }
        
        return $this->fetchAll($query);
    }
    public function countSearchData($keyword, $categoryId, $projectId, $owner, $project){
        $query = $this->select()
                        ->setIntegrityCheck(false)
                        ->from(array('e' => $this->_name), 'count(distinct e.equipmentId) as Total')
                        ->joinLeft(array('c'=>'category'), 'c.categoryId = e.categoryId' , array('categoryName'))
                        ->joinInner(array('eq'=>'equipment_project'), 'eq.equipmentId = e.equipmentId and eq.projectId IN ('.implode(',' ,$project).')', array())
                        ->joinInner(array('p'=>'project'), 'eq.projectId = p.projectId', array())
                        ->joinInner(array('i'=>'items'), 'e.equipmentId = i.equipmentId', array());
        if($keyword){
            $query->where("e.equipmentName LIKE ? OR e.code LIKE ? OR e.function LIKE ? OR e.position LIKE ? 
    					OR e.note LIKE ? OR e.ip LIKE ? OR e.iplan1 LIKE ? OR e.iplan2 LIKE ? OR e.endUser LIKE ? 
                        OR i.itemName LIKE ? OR i.history LIKE ? OR i.info LIKE ? OR i.owner LIKE ? 
                        OR p.projectName LIKE ?","%$keyword%");				
        }
        if($categoryId){
            if(is_array($categoryId)){
                $query->where('e.categoryId IN ('.implode(",",$categoryId).')');
            }else{
                $query->where('e.categoryId = ?',$categoryId);
            }
        }
        if($projectId){
            if(is_array($projectId)){
                $query->where('eq.projectId IN ('.implode(",",$projectId).')');
            }else{
                $query->where('eq.projectId = ?',$projectId);
            }
        }
        if($owner){
            foreach($owner as $key=>$row){
                $owner[$key] = '"'.$row.'"';
            }
            $query->where('i.owner IN ('.implode(",",$owner).')');
        }
        
        return $this->fetchRow($query);
    }
    public function getExcelData($keyword, $categoryId, $projectId, $owner, $project){
        $query = $this->select()
                        ->setIntegrityCheck(false)
                        ->from(array('e' => $this->_name))
                        ->joinLeft(array('c'=>'category'), 'c.categoryId = e.categoryId' , array('categoryName'))
                        ->joinInner(array('eq'=>'equipment_project'), 'eq.equipmentId = e.equipmentId and eq.projectId IN ('.implode(',' ,$project).')', array())
                        ->joinInner(array('p'=>'project'), 'eq.projectId = p.projectId', array())
                        ->joinInner(array('i'=>'items'), 'e.equipmentId = i.equipmentId', array())
                        ->group('e.equipmentId');
        if($keyword){
            $query->where("e.equipmentName LIKE ? OR e.code LIKE ? OR e.function LIKE ? OR e.position LIKE ? 
    					OR e.note LIKE ? OR e.ip LIKE ? OR e.iplan1 LIKE ? OR e.iplan2 LIKE ? OR e.endUser LIKE ? 
                        OR i.itemName LIKE ? OR i.history LIKE ? OR i.info LIKE ? OR i.owner LIKE ? 
                        OR p.projectName LIKE ?","%$keyword%");		
        }
        if($categoryId){
            if(is_array($categoryId)){
                $query->where('e.categoryId IN ('.implode(",",$categoryId).')');
            }else{
                $query->where('e.categoryId = ?',$categoryId);
            }
        }
        if($projectId){
            if(is_array($projectId)){
                $query->where('eq.projectId IN ('.implode(",",$projectId).')');
            }else{
                $query->where('eq.projectId = ?',$projectId);
            }
        }
        if($owner){
            foreach($owner as $key=>$row){
                $owner[$key] = '"'.$row.'"';
            }
            $query->where('i.owner IN ('.implode(",",$owner).')');
        }
        return $this->fetchAll($query);
    }
    public function checkCode($code){
        $query = $this->select();
        $query->from(array('e' => $this->_name), array('code'))
                ->where('code = ?',$code);
                //echo $query;exit;
        return $this->fetchRow($query);
    }
    public function checkMaxCode($code){
        $query = $this->select();
        $query->from(array('e' => $this->_name), array('code'))
                ->where('code like "'.$code.'%"')
                ->order('code DESC')
                ->limit('0,1');
                //echo $query;exit;
        return $this->fetchRow($query);
    }
    public function getDataById ($id)
    {
        $query = $this->select();
    	$query->from(array('e' => $this->_name))
    			->joinLeft(array('c'=>'category'), 'c.categoryId = e.categoryId' , array('categoryName'))
				->joinLeft(array('ep'=>'equipment_project'), 'ep.equipmentId = e.equipmentId' , array('projectId'))
    			->where('e.equipmentId = ?',$id)
    			->group ('e.equipmentId')
    			->setIntegrityCheck( false );

    	$rs = $this->fetchRow($query)->toArray();
		$item = $this->_getItems($rs['equipmentId']);
		$rs['item'] = $item;
        
		$project = $this->_getProject($rs['equipmentId']);
        $rs['project'] = $project;
		$project = $this->_getProject($rs['equipmentId']);
        $rs['projectId'] = array();
        foreach($project as $row){
            $rs['projectId'][] = $row['projectId'];
        }
        
    	return $rs;
    }
    private function _getItems ($equipmentId)
    {
		$db = $this->getDefaultAdapter();
		$sql = $db->select()->from('items')->where('equipmentId = ' .$equipmentId);
		$results = $db->query($sql);
		$stmt = $results->fetchAll();

   		return $stmt;
    }
    private function _getProject ($equipmentId)
    {
		$db = $this->getDefaultAdapter();
		$sql = $db->select()->from(array('p'=>'project'), array('p.projectName'))
                    ->joinLeft(array('ep'=>'equipment_project'), 'p.projectId = ep.projectId')
                    ->where('ep.equipmentId = '.$equipmentId);
		$results = $db->query($sql);
		$stmt = $results->fetchAll();

   		return $stmt;
    }
    private function _getProjectID ($equipmentId)
    {
		$db = $this->getDefaultAdapter();
		$sql = $db->select()->from(array('p'=>'project'), array('p.projectId'))
                    ->joinLeft(array('ep'=>'equipment_project'), 'p.projectId = ep.projectId', array())
                    ->where('ep.equipmentId = '.$equipmentId);
		$results = $db->query($sql);
		$stmt = $results->fetchAll();

   		return $stmt;
    }
    
    public function addEquipment ($data, $info, $owner, $history, $startDate, $projects, $item, $priceItem, $userId)
    {       
        try{
        	$id = $this->insert( $data );
			//insert items
        	$count = count($item);
        	$dataItem = array();
        	$db = $this->getAdapter();
        	for($i=0;$i<$count;++$i){
        		if(!$item[$i] && !$priceItem[$i] && !$info[$i] && !$owner[$i] && !$history[$i] && !$startDate[$i]){
        			continue;
        		}
        		$dataItem[] = '('.$db->quote($item[$i]) . ',' .$db->quote($priceItem[$i]) . ',' .$db->quote($id) . ',' .$db->quote($startDate[$i]). ',' .$db->quote($history[$i]). ',' .$db->quote($info[$i]). ',' .$db->quote($owner[$i]).',' .$db->quote($data['createUser']).',' .$db->quote($data['createDate']).')';
        	}
                
        	if(count($dataItem)){
        		$sql = 'INSERT INTO items (itemName, priceItem, equipmentId, startDate, history, info, owner, createUser, createDate) VALUES ' . implode(',', $dataItem);
                $stmt = $this->getAdapter()->prepare($sql);

				$stmt->execute();
        	}
        	//add project
        	$epModel = new It_Model_EquipmentProject();
        	foreach ($projects as $p){
        		$epModel->addEquipmentProject($id, $p);
            }
            
      		$historyModel = new It_Model_History();
      		$dataHistory = array('username' => $userId, 'action' => 'add', 'equipmentName' => $data['code'], 'equipmentId' => $id, 
                                'date' => date("Y-m-d H:i:s"));

      		$historyModel->addHistory($dataHistory);
            return $id;
        }
        catch(Zend_Exception $exp)
        {
        	echo 'Error!: ' . $exp->getMessage();
        	return $exp->getCode();
        }
    }
    public function updateEquipment ($data, $info, $owner, $history, $startDate, $itemId, $item, $priceItem, $item_new, $priceItem_new, $startDate_new, $info_new, $history_new, $owner_new, $userId, $projectId)
    {
        try{
			$equipmentId = $data['equipmentId'];
			unset($data['equipmentId']);
        	//Thêm history
			$dataOld = $this->getDataById($equipmentId);
			$equipmentField = array();
			$valueOld = array();
			$valueNew = array();
			unset($dataOld['item'],$dataOld['equipmentId'],$dataOld['categoryName']);			
			foreach($dataOld as $key=>$value){
				if(isset($data[$key]) && isset($dataOld[$key])){
					if($data[$key] != $dataOld[$key]){					
						$equipmentField[] = $key;
						$valueOld[] = $value;
						if($data[$key] == ''){
							$valueNew[] = "null";
						}else{
							$valueNew[] = $data[$key];
						}
					}
				}
			}			
			for($i = 0, $count = count($equipmentField); $i < $count; ++$i){
				if($equipmentField[$i] == "categoryId"){
					$model_category = new It_Model_Category();
					$catOld = $model_category->getDataByID($valueOld[$i]);
					$valueOld[$i] = $catOld['categoryName'];
					$catNew = $model_category->getDataByID($valueNew[$i]);
					$valueNew[$i] = $catNew['categoryName'];
				}
			}			
			$equipmentField = json_encode($equipmentField);
			$countHistory = count($valueOld);
        	$valueOld = json_encode($valueOld);
        	$valueNew = json_encode($valueNew);
        	//thêm history			
        	$this->update( $data, 'equipmentId = ' . (int) $equipmentId ); //cập nhật equipment
        	$historyModel = new It_Model_History();
			if($countHistory > 0){				
				$dataHistory = array('username' => $userId, 'action' => 'update', 'equipmentName' => $data['code'], 'equipmentId' => $equipmentId, 'equipmentField' => $equipmentField, 'valueOld' => $valueOld, 'valueNew' => $valueNew, 'date' => date("Y-m-d H:i:s"));
				$historyModel->addHistory($dataHistory);
			}

        	//update items
        	$count = count($itemId);
			$itemEq = $this->_getItems($equipmentId);			
			$itemOld = array();
			for($i=0; $i<count($itemEq); $i++){
				$itemOld[] = $itemEq[$i]['itemId'];
			}
            if(!is_array($itemId))
                $itemId = array();
			$diff = array_diff($itemOld, $itemId); // lấy các item đã bị xóa
			$diff = array_values($diff);
			$intersect = array_intersect($itemOld, $itemId); // lấy danh sách item thay đổi thông tin
        	$intersect = array_values($intersect);
			$dataItem = array();
        	$db = $this->getAdapter();
			if($diff){
				for($i=0;$i<count($diff);++$i){

					$sql = 'DELETE FROM items where itemId = '. $db->quote($diff[$i]);

					$stmt = $this->getAdapter()->prepare($sql);

					$stmt->execute();
				}
			}			
			if($intersect){
				for($i=0;$i<count($intersect);++$i){					
					if(!$item[$i] && !$priceItem[$i] && !$info[$i] && !$owner[$i] && !$history[$i] && !$startDate[$i]){
						continue;
					}
					$sql = 'UPDATE items set itemName ='.$db->quote($item[$i]).','.'priceItem ='.$db->quote($priceItem[$i]).','.'startDate ='.$db->quote($startDate[$i]).','.'history ='.$db->quote($history[$i]).','.'info ='.$db->quote($info[$i]).','.'owner ='.$db->quote($owner[$i]).' '.' where itemId ='.$intersect[$i];
                    
					$stmt = $this->getAdapter()->prepare($sql);

					$stmt->execute();							
				}
			}

        	if($item_new){
				//insert item new
	        	$count = count($item_new);

	        	$dataItem = array();
	        	$db = $this->getAdapter();
	        	for($i=0;$i<$count;++$i){
	        		if(!$item_new[$i] && !$priceItem_new[$i] && !$startDate_new[$i] && !$info_new[$i] && !$history_new[$i] && !$owner_new[$i]){
	        			continue;
	        		}
	        		$dataItem[] = '('.$db->quote($item_new[$i]) . ',' .$db->quote($priceItem_new[$i]) . ',' .$db->quote($equipmentId) . ',' .$db->quote($startDate_new[$i]). ',' .$db->quote($history_new[$i]). ',' .$db->quote($info_new[$i]) . ',' .$db->quote($owner_new[$i]) . ',' . $db->quote($userId) . ',' . $db->quote(date("Y-m-d H:i:s")) .')';
	        	}

	        	if(count($dataItem)){
	        		$sql = 'INSERT INTO items (itemName, priceItem, equipmentId, startDate, history, info, owner, createUser, createDate) VALUES ' . implode(',', $dataItem);
					
                    $stmt = $this->getAdapter()->prepare($sql);
					$stmt->execute();
                    
    				$dataHistory = array(
                                'username' => $userId, 'action' => 'add item', 'equipmentName' => $data['equipmentName'], 'equipmentId' => $equipmentId, 
                                'date' => date("Y-m-d H:i:s"));				
    				$historyModel->addHistory($dataHistory);
	        	}
        	}
			
			//update project
			$ep 			= new It_Model_EquipmentProject();
			$projectOld 	= $ep->getProjectId($equipmentId);			
			$model_project 	= new It_Model_Project();
            
			$projectNameOld = $model_project->getDataByEquipmentId($equipmentId);
            $str = "";
            foreach($projectNameOld as $row){
                if($str != "")
                    $str .= ", ";
                $str .= $row['projectName'];
            }
            $projectNameOld = "[\"".$str."\"]";
            
            $epModel = new It_Model_EquipmentProject();
            $epModel->deleteProjectByEquipmentId($equipmentId);
        	foreach ($projectId as $p){
        		$epModel->addEquipmentProject($equipmentId, $p);
            }		
			$projectNameNew = $model_project->getDataByEquipmentId($equipmentId)->toArray();
            
            $str = "";
            foreach($projectNameNew as $row){
                if($str != "")
                    $str .= ", ";
                $str .= $row['projectName'];
            }
            $projectNameNew = "[\"".$str."\"]";
            
            
			// pr(json_encode(array($projectNameOld['projectName'])));
			if($projectNameOld != $projectNameNew){
				$dataHistory = array(
                            'username' => $userId, 'action' => 'update', 'equipmentName' => $data['equipmentName'], 'equipmentId' => $equipmentId, 
                            'equipmentField' => json_encode(array('projectId')), 
                            'valueOld' => $projectNameOld, 
                            'valueNew' => $projectNameNew, 
                            'date' => date("Y-m-d H:i:s"));				
				$historyModel->addHistory($dataHistory);
			}
        }
        catch(Zend_Exception $exp)
        {
        	print($exp);exit;
			return $exp->getCode();
        }
    }
    public function deleteEquipment ($id, $userId)
    {
        $query = $this->select()->from(array('e' => $this->_name) , array('equipmentName'))->where('equipmentId = ? ', $id);
		
		$result = $this->fetchRow($query);
        
		$this->delete( 'equipmentId =' . (int) $id );
		
		// xóa các item thuộc equipment
		$sql = 'DELETE FROM items WHERE equipmentId = ' . $id;
		$stmt = $this->getAdapter()->prepare($sql);
		$stmt->execute();

        $historyModel = new It_Model_History();

  		$dataHistory = array('username' => $userId, 'action' => 'delete', 'equipmentName' => $result->equipmentName, 'equipmentId' => $id, 'date' => date("Y-m-d H:i:s"));

  		$historyModel->addHistory($dataHistory);
		
		$this->delete( 'equipmentId =' . (int) $id );		
    }
}

























